﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    public class HisEducationDAL : IHisEducationDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public HisEducationDAL()
        { 
        
        }
        public IList<HisEducationInfo> ListHisEducations(HisEducationInfo hisInfo)
        {
            string strSQL;
            int empSeq = hisInfo.Employee == null ? 0 : hisInfo.Employee.Sequence;

            List<HisEducationInfo> infoList = new List<HisEducationInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", hisInfo.Sequence);
                dbManager.AddParameters(2, "@empSequence", empSeq);

                strSQL = "SELECT h.EDU_SEQ, h.EMP_SEQ, h.EDU_DEGREE_SEQ, h.EDU_QUAL_SEQ, h.EDU_MAJOR_SEQ"
                        + ", h.EDU_INS_SEQ, h.EDU_START_YEAR, h.EDU_END_YEAR, h.START_DATE, h.END_DATE"
                        + ", h.GPA, h.REMARKS, h.RECORD_STATUS, h.CREATED_BY, h.UPDATED_BY"
                    + " FROM HRM_PS_EMP_EDUCATIONS h"
                    + " WHERE h.RECORD_STATUS not like @DeleteRecord"
                    + (hisInfo.Sequence > 0 ? " AND h.EDU_SEQ = @Sequence" : "")
                    + (empSeq > 0 ? " AND h.EMP_SEQ = @empSequence" : "")
                    + " ORDER BY h.END_DATE, h.EDU_END_YEAR";

                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

                while (rdr.Read())
                {
                    EmployeeDAL empDAL = new EmployeeDAL();
                    EmployeeInfo empInfo = new EmployeeInfo();
                    if (!rdr.IsDBNull(1))
                    {
                        empInfo.Sequence = rdr.GetInt32(1);
                        //IList<EmployeeInfo> empList = empDAL.ListEmployees(new EmployeeInfo(rdr.GetInt32(1)));
                        //empInfo = empList[0];
                    }

                    MasEducationDegreeDAL degreeDAL = new MasEducationDegreeDAL();
                    MasEducationDegreeInfo degreeInfo = new MasEducationDegreeInfo();
                    if (!rdr.IsDBNull(2))
                    {
                        IList<MasEducationDegreeInfo> degreeList = degreeDAL.ListEducationDegree(new MasEducationDegreeInfo(rdr.GetInt32(2)));
                        degreeInfo = degreeList.Count > 0 ? degreeList[0] : null;
                    }

                    MasEducationQualificationDAL qualDAL = new MasEducationQualificationDAL();
                    MasEducationQualificationInfo qualInfo = new MasEducationQualificationInfo();
                    if (!rdr.IsDBNull(3))
                    {
                        IList<MasEducationQualificationInfo> qualList = qualDAL.ListEducationQualifications(new MasEducationQualificationInfo(rdr.GetInt32(3)));
                        qualInfo = qualList.Count > 0 ? qualList[0] : null;
                    }

                    MasEducationMajorDAL majorDAL = new MasEducationMajorDAL();
                    MasEducationMajorInfo majorInfo = new MasEducationMajorInfo();
                    if (!rdr.IsDBNull(4))
                    {
                        IList<MasEducationMajorInfo> majorList = majorDAL.ListEducationMajors(new MasEducationMajorInfo(rdr.GetInt32(4)));
                        majorInfo = majorList.Count > 0 ? majorList[0] : null;
                    }

                    MasEducationInstituteDAL insDAL = new MasEducationInstituteDAL();
                    MasEducationInstituteInfo insInfo = new MasEducationInstituteInfo();
                    if (!rdr.IsDBNull(5) && rdr.GetInt32(5) > 0)
                    {
                        IList<MasEducationInstituteInfo> insList = insDAL.ListEducationInstitutes(new MasEducationInstituteInfo(rdr.GetInt32(5)));
                        insInfo = insList.Count > 0 ? insList[0] : null;
                    }
                    HisEducationInfo Info = new HisEducationInfo(rdr.GetInt32(0)
                            , empInfo, degreeInfo, qualInfo, majorInfo, insInfo
                            , rdr.IsDBNull(6) ? (int?)null : rdr.GetInt32(6)
                            , rdr.IsDBNull(7) ? (int?)null : rdr.GetInt32(7)
                            , rdr.IsDBNull(8) ? (DateTime?)null : rdr.GetDateTime(8)
                            , rdr.IsDBNull(9) ? (DateTime?)null : rdr.GetDateTime(9)
                            , rdr.IsDBNull(10) ? (decimal?)null : rdr.GetDecimal(10)
                            , rdr.IsDBNull(11) ? "" : rdr.GetString(11)
                            , rdr.IsDBNull(12) ? "" : rdr.GetString(12)
                            , rdr.IsDBNull(13) ? (int?)null : rdr.GetInt32(13)
                            , rdr.IsDBNull(14) ? (int?)null : rdr.GetInt32(14));
                    infoList.Add(Info);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }
        public DataSet LoadEducationHistories(HisEducationInfo hisInfo)
        {
            string strSQL;
            int empSeq = hisInfo.Employee == null ? 0 : hisInfo.Employee.Sequence;

            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@Sequence", hisInfo.Sequence);
                dbManager.AddParameters(2, "@empSequence", empSeq);

                strSQL = "SELECT h.EDU_SEQ, h.EMP_SEQ, h.EDU_DEGREE_SEQ, h.EDU_QUAL_SEQ, h.EDU_MAJOR_SEQ"
                        + ", h.EDU_INS_SEQ, h.EDU_START_YEAR, h.EDU_END_YEAR, h.START_DATE, h.END_DATE"
                        + ", h.GPA, h.REMARKS, h.RECORD_STATUS, h.CREATED_BY, h.UPDATED_BY"
                        + ", q.EDU_QUAL_NAME, m.EDU_MAJOR_NAME, i.EDU_INS_NAME, dg.EDU_DEGREE_NAME"
                        + ", i.COUNTRY_SEQ "
                    + " FROM HRM_PS_EMP_EDUCATIONS h"
                        + " LEFT JOIN MAS_EDU_QUALIFICATIONS q ON h.EDU_QUAL_SEQ = q.EDU_QUAL_SEQ"
                        + " LEFT JOIN MAS_EDU_DEGREES dg ON q.EDU_DEGREE_SEQ = dg.EDU_DEGREE_SEQ"
                        + " LEFT JOIN MAS_EDU_MAJORS m ON h.EDU_MAJOR_SEQ = m.EDU_MAJOR_SEQ"
                        + " LEFT JOIN MAS_EDU_INSTITUTES i ON h.EDU_INS_SEQ = i.EDU_INS_SEQ"
                        //+ " LEFT JOIN MAS_COUNTRIES c ON i.COUNTRY_SEQ = c.COUNTRY_SEQ"
                    + " WHERE h.RECORD_STATUS = @ActiveRecord"
                    + (hisInfo.Sequence > 0 ? " AND h.EDU_SEQ = @Sequence" : "")
                    + (empSeq > 0 ? " AND h.EMP_SEQ = @empSequence" : "")
                    + " ORDER BY h.END_DATE, h.EDU_END_YEAR";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return ds;
        }
        public void Insert(HisEducationInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee.Sequence);
                dbManager.AddParameters(1, "@Gpa", Info.GPA);
                dbManager.AddParameters(2, "@EduDegreeSeq", Info.Degree == null ? null : Info.Degree.Sequence);
                dbManager.AddParameters(3, "@EduMajorSeq", Info.Major == null ? (int?)null : Info.Major.Sequence);
                dbManager.AddParameters(4, "@EduQualSeq", Info.Qualification == null ? null : Info.Qualification.Sequence);
                dbManager.AddParameters(5, "@EduInsSeq", Info.Institute == null ? null : Info.Institute.Sequence);
                dbManager.AddParameters(6, "@EduStartYear", Info.EduStartYear);
                dbManager.AddParameters(7, "@EduEndYear", Info.EduEndYear);
                dbManager.AddParameters(8, "@StartDate", Info.StartDate);
                dbManager.AddParameters(9, "@EndDate", Info.EndDate);
                dbManager.AddParameters(10, "@Remarks", Info.Remarks);
                dbManager.AddParameters(11, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(12, "@CreatedBy", Info.CreatedBy);
                dbManager.AddParameters(13, "@CreatedWhen", DateTime.Today);

                strSQL = "INSERT INTO HRM_PS_EMP_EDUCATIONS(EMP_SEQ, GPA"
                        + ", EDU_DEGREE_SEQ, EDU_MAJOR_SEQ, EDU_QUAL_SEQ, EDU_INS_SEQ"
                        + ", EDU_START_YEAR, EDU_END_YEAR, START_DATE, END_DATE"
                        + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " VALUES(@EmpSeq, @Gpa, @EduDegreeSeq, @EduMajorSeq, @EduQualSeq"
                        + ", @EduInsSeq, @EduStartYear, @EduEndYear, @StartDate, @EndDate"
                        + ", @Remarks, @RecordStatus, @CreatedBy, @CreatedWhen)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(HisEducationInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@EmpSeq", Info.Employee.Sequence);
                dbManager.AddParameters(1, "@Gpa", Info.GPA);
                dbManager.AddParameters(2, "@EduDegreeSeq", Info.Degree == null ? null : Info.Degree.Sequence);
                dbManager.AddParameters(3, "@EduMajorSeq", Info.Major == null ? (int?)null : Info.Major.Sequence);
                dbManager.AddParameters(4, "@EduQualSeq", Info.Qualification == null ? null : Info.Qualification.Sequence);
                dbManager.AddParameters(5, "@EduInsSeq", Info.Institute == null ? null : Info.Institute.Sequence);
                dbManager.AddParameters(6, "@EduStartYear", Info.EduStartYear);
                dbManager.AddParameters(7, "@EduEndYear", Info.EduEndYear);
                dbManager.AddParameters(8, "@StartDate", Info.StartDate);
                dbManager.AddParameters(9, "@EndDate", Info.EndDate);
                dbManager.AddParameters(10, "@Remarks", Info.Remarks);
                dbManager.AddParameters(11, "@UpdatedBy", Info.UpdatedBy);
                dbManager.AddParameters(12, "@UpdatedWhen", DateTime.Today);
                dbManager.AddParameters(13, "@Sequence", Info.Sequence);

                strSQL = "UPDATE HRM_PS_EMP_EDUCATIONS"
                    + " SET EMP_SEQ = @EmpSeq"
                        + ", GPA = @Gpa"
                        + ", EDU_DEGREE_SEQ = @EduDegreeSeq"
                        + ", EDU_MAJOR_SEQ = @EduMajorSeq"
                        + ", EDU_QUAL_SEQ = @EduQualSeq"
                        + ", EDU_INS_SEQ = @EduInsSeq"
                        + ", EDU_START_YEAR = @EduStartYear"
                        + ", EDU_END_YEAR = @EduEndYear"
                        + ", START_DATE = @StartDate"
                        + ", END_DATE = @EndDate"
                        + ", REMARKS = @Remarks"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE EDU_SEQ = @Sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@seq", Sequence);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMP_EDUCATIONS"
                    + " SET RECORD_STATUS = @record_status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE EDU_SEQ = @seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
